In [6]:
import pandas as pd
CSV_PATH = "att_2025-05-24_converted(in).csv"
df = pd.read_csv(CSV_PATH)
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")
# Drop the spacer/junk rows (keep only real location records)
df_clean = df.dropna(subset=["device_lat", "device_lon"]).copy()
df_clean["row_id"] = pd.to_numeric(df_clean["row_id"], errors="coerce")
df_clean = df_clean.dropna(subset=["row_id"]).copy()
df_clean.reset_index(drop=True, inplace=True)
print("Raw rows:", len(df))
print("Clean rows:", len(df_clean))
df_clean.head(272)
Raw rows: 544 Clean rows: 272
Out[6]:
| row_id | conn_date | conn_time_utc | timing_advance | cell_id | network_type | tower_lat | tower_lon | sector_orientation | device_lat | device_lon | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 318.0 | 5/24/2025 | 0:04:03 | 3.0 | 18497943.0 | 4G | 39.343300 | -76.729202 | 240.0 | 39.343746 | -76.731483 |
| 1 | 319.0 | 5/24/2025 | 0:04:18 | 3.0 | 18497802.0 | 4G | 39.343300 | -76.729202 | 240.0 | 39.344002 | -76.731606 |
| 2 | 320.0 | 5/24/2025 | 0:04:41 | 3.0 | 18497943.0 | 4G | 39.343300 | -76.729202 | 240.0 | 39.343746 | -76.731483 |
| 3 | 321.0 | 5/24/2025 | 0:05:08 | 3.0 | 18497802.0 | 4G | 39.343300 | -76.729202 | 240.0 | 39.344002 | -76.731606 |
| 4 | 322.0 | 5/24/2025 | 0:06:21 | 3.0 | 18497943.0 | 4G | 39.343300 | -76.729202 | 240.0 | 39.343746 | -76.731483 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 267 | 585.0 | 5/24/2025 | 23:55:17 | 13.0 | 18422808.0 | 4G | 39.392778 | -76.789167 | 240.0 | 39.391052 | -76.799850 |
| 268 | 586.0 | 5/24/2025 | 23:55:54 | 13.0 | 18422794.0 | 4G | 39.392778 | -76.789167 | 240.0 | 39.384132 | -76.788139 |
| 269 | 587.0 | 5/24/2025 | 23:56:21 | 13.0 | 18423008.0 | 4G | 39.392778 | -76.789167 | 240.0 | 39.390850 | -76.799789 |
| 270 | 588.0 | 5/24/2025 | 23:57:15 | 14.0 | 18422808.0 | 4G | 39.392778 | -76.789167 | 240.0 | 39.391052 | -76.799850 |
| 271 | 589.0 | 5/24/2025 | 23:58:14 | 14.0 | 18422794.0 | 4G | 39.392778 | -76.789167 | 240.0 | 39.384132 | -76.788139 |
272 rows × 11 columns
In [17]:
import pandas as pd
import plotly.express as px
import plotly.io as pio
pio.renderers.default = "notebook"
# Load data
df = pd.read_csv("att_2025-05-24_converted(in).csv")
# Force numeric
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")
# Keep only valid device locations
df_clean = df.dropna(subset=["device_lat", "device_lon"])
# Claimed location
his_lat = 39.32148
his_lon = -76.61831
# Build interactive plot — My DATA
fig = px.scatter(
df_clean,
x="device_lon",
y="device_lat",
hover_data={
"conn_time_utc": True,
"device_lat": True,
"device_lon": True,
"cell_id": True,
"network_type": True
},
title="AT&T Device Location Points (May 24, 2025)",
)
# Explicitly style + AT&T data
fig.update_traces(
marker=dict(size=8, color="blue", opacity=0.7),
name="My AT&T Device Location Data",
showlegend=True
)
# Add claimed location (his)
fig.add_scatter(
x=[his_lon],
y=[his_lat],
mode="markers",
marker=dict(size=12, color="red", symbol="x"),
name="Claimed Location (His Coordinates)"
)
# Zoom to Baltimore
fig.update_layout(
xaxis_title="Longitude",
yaxis_title="Latitude",
xaxis=dict(range=[-76.85, -76.55]),
yaxis=dict(range=[39.25, 39.45]),
legend=dict(
title="Legend",
yanchor="top",
y=0.99,
xanchor="left",
x=0.01
)
)
fig.show()
In [24]:
import pandas as pd
import plotly.express as px
# Load data
df = pd.read_csv("att_2025-05-24_converted(in).csv")
# Force numeric
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")
df_clean = df.dropna(subset=["device_lat", "device_lon"])
# Claimed location
his_lat = 39.32148
his_lon = -76.61831
# Map of Baltimore with AT&T points
fig = px.scatter_mapbox(
df_clean,
lat="device_lat",
lon="device_lon",
hover_data={
"conn_time_utc": True,
"device_lat": True,
"device_lon": True,
"cell_id": True
},
zoom=10,
height=650,
title="AT&T Device Location Data — Baltimore (May 24, 2025)"
)
# Style AT&T points
fig.update_traces(
marker=dict(size=8, color="blue", opacity=0.7),
name="My AT&T Device Location Data",
showlegend=True
)
# Add claimed location
fig.add_scattermapbox(
lat=[his_lat],
lon=[his_lon],
mode="markers",
marker=dict(size=6, color="red"),
name="Claimed Location (His Coordinates)"
)
# Map settings
fig.update_layout(
mapbox_style="open-street-map",
mapbox_center={"lat": 39.2904, "lon": -76.6122}, # Baltimore center
legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01)
)
fig.show()
/tmp/ipykernel_3487/3232317641.py:18: DeprecationWarning: *scatter_mapbox* is deprecated! Use *scatter_map* instead. Learn more at: https://plotly.com/python/mapbox-to-maplibre/
In [25]:
import numpy as np
# Haversine distance (meters)
def haversine(lat1, lon1, lat2, lon2):
R = 6371000 # Earth radius in meters
phi1 = np.radians(lat1)
phi2 = np.radians(lat2)
dphi = np.radians(lat2 - lat1)
dlambda = np.radians(lon2 - lon1)
a = np.sin(dphi/2)**2 + np.cos(phi1)*np.cos(phi2)*np.sin(dlambda/2)**2
return 2 * R * np.arcsin(np.sqrt(a))
# Claimed coordinates
his_lat = 39.32148
his_lon = -76.61831
# Compute distance for every point
df_clean["distance_meters"] = haversine(
df_clean["device_lat"],
df_clean["device_lon"],
his_lat,
his_lon
)
# Identify nearest point
nearest_point = df_clean.loc[df_clean["distance_meters"].idxmin()]
nearest_point
/tmp/ipykernel_3487/869805969.py:19: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
Out[25]:
row_id 517.0 conn_date 5/24/2025 conn_time_utc 22:03:06 timing_advance 11.0 cell_id 18513672.0 network_type 4G tower_lat 39.316101 tower_lon -76.631897 sector_orientation 31.0 device_lat 39.321331 device_lon -76.623268 distance_meters 426.812755 Name: 399, dtype: object
In [29]:
import pandas as pd
import plotly.graph_objects as go
from math import radians, cos, sin, asin, sqrt
# -----------------------------
# Helper: Haversine distance (meters)
# -----------------------------
def haversine(lat1, lon1, lat2, lon2):
R = 6371000 # Earth radius in meters
lat1, lon1, lat2, lon2 = map(radians, [lat1, lon1, lat2, lon2])
dlat = lat2 - lat1
dlon = lon2 - lon1
a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
return 2 * R * asin(sqrt(a))
# -----------------------------
# Load AT&T data
# -----------------------------
df = pd.read_csv("att_2025-05-24_converted(in).csv")
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")
df_clean = df.dropna(subset=["device_lat", "device_lon"])
# -----------------------------
# Claimed location (his)
# -----------------------------
his_lat = 39.32148
his_lon = -76.61831
# -----------------------------
# Find nearest AT&T point
# -----------------------------
df_clean["distance_meters"] = df_clean.apply(
lambda r: haversine(r["device_lat"], r["device_lon"], his_lat, his_lon),
axis=1
)
nearest = df_clean.loc[df_clean["distance_meters"].idxmin()]
# -----------------------------
# Build map (ONLY TWO POINTS)
# -----------------------------
fig = go.Figure()
# Nearest AT&T point (Me)
fig.add_trace(go.Scattermapbox(
lat=[nearest["device_lat"]],
lon=[nearest["device_lon"]],
mode="markers",
marker=dict(size=16, color="blue"),
name="Nearest AT&T Device Location",
hovertext=(
f"Device Location<br>"
f"Lat: {nearest['device_lat']}<br>"
f"Lon: {nearest['device_lon']}<br>"
f"Time (UTC): {nearest['conn_time_utc']}<br>"
f"Distance: {round(nearest['distance_meters'],1)} meters"
)
))
# Claimed location (HIM)
fig.add_trace(go.Scattermapbox(
lat=[his_lat],
lon=[his_lon],
mode="markers",
marker=dict(size=16, color="red"),
name="Claimed Location",
hovertext=f"Claimed Location<br>Lat: {his_lat}<br>Lon: {his_lon}"
))
# -----------------------------
# Map settings — FORCE Baltimore
# -----------------------------
fig.update_layout(
title="Claimed Location vs Nearest AT&T Device Location (May 24, 2025)",
mapbox=dict(
style="open-street-map",
center=dict(lat=his_lat, lon=his_lon),
zoom=16
),
margin=dict(l=0, r=0, t=50, b=0),
legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01)
)
fig.show()
/tmp/ipykernel_3487/1199652861.py:35: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy /tmp/ipykernel_3487/1199652861.py:48: DeprecationWarning: *scattermapbox* is deprecated! Use *scattermap* instead. Learn more at: https://plotly.com/python/mapbox-to-maplibre/ /tmp/ipykernel_3487/1199652861.py:64: DeprecationWarning: *scattermapbox* is deprecated! Use *scattermap* instead. Learn more at: https://plotly.com/python/mapbox-to-maplibre/
In [37]:
import pandas as pd
import plotly.express as px
import plotly.io as pio
pio.renderers.default = "notebook"
# -----------------------------
# 1) Load CSV
# -----------------------------
df = pd.read_csv("att_2025-05-24_converted(in).csv")
# Force numeric
df["device_lat"] = pd.to_numeric(df["device_lat"], errors="coerce")
df["device_lon"] = pd.to_numeric(df["device_lon"], errors="coerce")
df["row_id"] = pd.to_numeric(df["row_id"], errors="coerce")
# Keep only valid device points
df_clean = df.dropna(subset=["device_lat", "device_lon", "row_id"]).copy()
# -----------------------------
# 2) Claimed location (his)
# -----------------------------
his_lat = 39.32148
his_lon = -76.61831
# -----------------------------
# 3) Filter rows 517–525
# -----------------------------
subset = df_clean[(df_clean["row_id"] >= 517) & (df_clean["row_id"] <= 525)].copy()
# Add a label so Plotly can color the two groups
subset["point_type"] = "AT&T device locations (rows 517–525)"
claimed = pd.DataFrame([{
"row_id": None,
"conn_time_utc": None,
"device_lat": his_lat,
"device_lon": his_lon,
"point_type": "Claimed location (his coordinates)"
}])
plot_df = pd.concat([subset, claimed], ignore_index=True)
# -----------------------------
# 4) Map plot (Baltimore zoom)
# -----------------------------
fig = px.scatter_map(
plot_df,
lat="device_lat",
lon="device_lon",
color="point_type",
hover_data={
"row_id": True,
"conn_time_utc": True,
"device_lat": ":.6f",
"device_lon": ":.6f"
},
zoom=12,
center={"lat": his_lat, "lon": his_lon},
map_style="open-street-map",
title="Claimed Location vs AT&T Device Locations (Rows 517–525) — May 24, 2025"
)
# BIG, CLEAR DOTS
fig.update_traces(marker=dict(size=18, opacity=0.9))
# Clean legend placement
fig.update_layout(
legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01)
)
fig.show()
/tmp/ipykernel_3487/2737704668.py:42: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.
In [ ]: